package nl.hro.mobiqueen.data;

import nl.hro.mobiqueen.data.objects.Event;
import nl.hro.mobiqueen.data.objects.Podium;
import nl.hro.mobiqueen.generic.ApplicationException;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseAdapter {
	private static final String TAG = "DatabaseAdapter";

	private static final String DATABASE_NAME = "mobiqueendb";
	private static final int DATABASE_VERSION = 2;

	private static final String TABLE_ARTIST = "artist";
	private static final String TABLE_EVENT = "event";
	private static final String TABLE_PODIUM = "podium";
	private static final String TABLE_FAVORITES = "favorites";

	public static final String KEY_ID = "id";
	public static final String KEY_NAME = "name";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";
	public static final String KEY_ICON_MAPS = "icon_maps";
	public static final String KEY_ARTIST = "artist";
	public static final String KEY_PODIUM = "podium";
	public static final String KEY_START_TIME = "start_time";
	public static final String KEY_END_TIME = "end_time";
	public static final String KEY_GENRE = "genre";
	public static final String KEY_FAVORITE = "favorite";

	public static final int IS_NOT_FAVORITE = 0;
	public static final int IS_FAVORITE = 1;

	private static final String DATABASE_CREATE_ARTIST = ""
			+ "CREATE TABLE artist ("
			+ "     id integer primary key autoincrement, "
			+ "     name varchar not null," + " description text not null);";

	private static final String DATABASE_CREATE_PODIUM = ""
			+ "CREATE TABLE podium ("
			+ " id integer primary key autoincrement, "
			+ " name varchar not null, " + " description varchar not null,"
			+ " latitude real, " + " longitude real, " + " icon_maps blob);";

	private static final String DATABASE_CREATE_EVENT = ""
			+ "CREATE TABLE event ("
			+ " id integer primary key autoincrement, " + " name varchar, "
			+ " podium integer not null, " + " artist integer not null, "
			+ " start_time varchar not null, " + " end_time varchar not null, "
			+ " genre varchar not null,"
			+ " FOREIGN KEY (podium) REFERENCES podium(id),"
			+ " FOREIGN KEY (artist) REFERENCES artist(id));";

	private static final String DATABASE_CREATE_FAVORITES = ""
			+ "CREATE TABLE favorites ("
			+ "     id integer primary key autoincrement, "
			+ "     name varchar not null,"
			+ "     favorite integer not null);";

	private final Context context;

	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;

	public DatabaseAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DATABASE_CREATE_ARTIST);
				db.execSQL(DATABASE_CREATE_PODIUM);
				db.execSQL(DATABASE_CREATE_EVENT);
				db.execSQL(DATABASE_CREATE_FAVORITES);
			} catch (SQLiteException e) {
				Log.e("createerr", e.toString());
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_ARTIST);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_PODIUM);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENT);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVORITES);
			onCreate(db);
		}
	}

	// ---opens the database---
	public DatabaseAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}

	/**
	 * Insert Favorite
	 */
	public void insertFavorite(String name, int favorite) {
		ContentValues values = new ContentValues();
		// values.put(KEY_ID, id);
		values.put(KEY_NAME, name);
		values.put(KEY_FAVORITE, favorite);
		db.insert(TABLE_FAVORITES, null, values);
	}

	public void updateFavorite(String name, int favorite) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_FAVORITE, favorite);
		db.update(TABLE_FAVORITES, values, KEY_NAME + "='" + name + "'", null);
	}

	/**
	 * find favorite
	 */
	public Cursor findFavoriteById(int id) {
		Cursor cursor = db.query(true, TABLE_FAVORITES, new String[] { KEY_ID,
				KEY_NAME, KEY_FAVORITE }, KEY_ID + "=" + id, null, null, null,
				null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor findFavoriteByName(String name) {
		Cursor cursor = db.query(true, TABLE_FAVORITES, new String[] { KEY_ID,
				KEY_NAME, KEY_FAVORITE }, KEY_NAME + "='" + name + "'", null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * toggleFavorite
	 */
        public int clearFavorites() {
            int favorite = 0;
            int rowsAffected = 0;

            ContentValues values = new ContentValues();
            values.put(KEY_FAVORITE, favorite);
            rowsAffected = db.update(TABLE_FAVORITES, values, KEY_FAVORITE + "=1", null);

            return rowsAffected;
        }

	public void toggleFavorite(String name) {
		Cursor c = findFavoriteByName(name);
                int favorite = 0;

		if (c != null) {
			if (c.moveToFirst()) {
				do {
					favorite = c.getInt(c.getColumnIndex(KEY_FAVORITE));
					favorite = favorite == 0 ? 1 : 0;

                                        // If the new favorite is '1', we need to reset all the
                                        // other favorites to '0'
                                        // The easiest way is to reset all to '0' and set the
                                        // required to '1', because: "There can only be one"
                                        if (favorite == 1)
                                        {
                                            clearFavorites();
                                        }

					updateFavorite(name, favorite);
					break;
				} while (c.moveToNext());
			}
		} else {
                        favorite = 1;
                        clearFavorites();
			insertFavorite(name, IS_FAVORITE);
                        updateFavorite(name, favorite);
		}

	}

	/**
	 * Delete Artist
	 */
	public void deleteArtist(int id) {
		db.delete(TABLE_ARTIST, KEY_ID + "=" + id, null);
	}

	public void deleteArtist(String name) {
		db.delete(TABLE_ARTIST, KEY_NAME + "='" + name + "'", null);
	}

	/**
	 * Delete Event
	 */
	public void deleteEvent(int id) {
		db.delete(TABLE_EVENT, KEY_ID + "=" + id, null);
	}

	public void deleteEvent(String name) {
		db.delete(TABLE_EVENT, KEY_NAME + "='" + name + "'", null);
	}

	/**
	 * Delete Podium
	 */
	public void deletePodium(int id) throws ApplicationException {
		Cursor c = getEventsForPodium(id);
		c.moveToFirst();
		boolean performanceExistsForPodium = c.getCount() > 0 ? true : false;
		if (performanceExistsForPodium)
			throw new ApplicationException(
					"Referential integrity fault. Delete not allowed.");

		db.delete(TABLE_PODIUM, KEY_ID + "=" + id, null);
	}

	public void deletePodium(String name) throws ApplicationException {
		Cursor c = getEventsForPodium(name);
		c.moveToFirst();
		boolean performanceExistsForPodium = c.getCount() > 0 ? true : false;
		if (performanceExistsForPodium)
			throw new ApplicationException(
					"Referential integrity fault. Delete not allowed.");

		db.delete(TABLE_PODIUM, KEY_NAME + "='" + name + "'", null);
	}

	/**
	 * Find Artist
	 */
	public Cursor findArtist(int id) {
		Cursor cursor = db.query(true, TABLE_ARTIST, new String[] { KEY_ID,
				KEY_NAME, KEY_DESCRIPTION }, KEY_ID + "=" + id, null, null,
				null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor findArtist(String name) {
		Cursor cursor = db.query(true, TABLE_ARTIST, new String[] { KEY_ID,
				KEY_NAME, KEY_DESCRIPTION }, KEY_NAME + "='" + name + "'",
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Find Event as Cursor
	 */
	public Cursor findEvent(int id) throws SQLException {
		Cursor cursor = db.query(true, TABLE_EVENT,
				new String[] { KEY_ID, KEY_PODIUM, KEY_ARTIST, KEY_START_TIME,
						KEY_END_TIME, KEY_GENRE }, KEY_ID + "=" + id, null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Find Podium
	 */
	public Cursor findPodium(int id) throws SQLException {
		Cursor cursor = db.query(true, TABLE_PODIUM, new String[] { KEY_ID,
				KEY_NAME, KEY_LATITUDE, KEY_LONGITUDE, KEY_ICON_MAPS }, KEY_ID
				+ "=" + id, null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor findPodium(String name) throws SQLException {
		Cursor cursor = db.query(true, TABLE_PODIUM, new String[] { KEY_ID,
				KEY_NAME, KEY_LATITUDE, KEY_LONGITUDE, KEY_ICON_MAPS },
				KEY_NAME + "='" + name + "'", null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Insert Artist
	 */
	public void insertArtist(int id, String name, String description) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		db.insert(TABLE_ARTIST, null, values);
	}

	/**
	 * Insert Event
	 */
	public void insertEvent(int id, int podium, int artist, String startTime,
			String endTime, String genre) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_PODIUM, podium);
		values.put(KEY_ARTIST, artist);
		values.put(KEY_START_TIME, startTime);
		values.put(KEY_END_TIME, endTime);
		values.put(KEY_GENRE, genre);
		db.insert(TABLE_EVENT, null, values);
	}

	/**
	 * Insert Podium
	 */
	public void insertPodium(int id, String name, String description,
			Double lat, Double lon, byte[] mapsIcon) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		values.put(KEY_LATITUDE, lat);
		values.put(KEY_LONGITUDE, lon);
		values.put(KEY_ICON_MAPS, mapsIcon);
		db.insert(TABLE_PODIUM, null, values);
	}

	/**
	 * List Artists
	 */
	public Cursor listArtists() {
		return db.query(TABLE_ARTIST, new String[] { KEY_ID, KEY_NAME,
				KEY_DESCRIPTION }, null, null, null, null, null);
	}

	/**
	 * List Events
	 */
	public Cursor listEvents() {
		return db.query(TABLE_EVENT, new String[] { KEY_ID, KEY_PODIUM,
				KEY_ARTIST, KEY_START_TIME, KEY_END_TIME, KEY_GENRE }, null,
				null, null, null, null);
	}

	public Cursor GetSinglePerformance(String name) {
		return db.rawQuery(
				"SELECT e.name, e.genre, p.latitude,p.longitude,e.start_time,e.end_time"
						+ "FROM " + TABLE_EVENT + " e " + "     ,"
						+ TABLE_PODIUM + " p " + "WHERE e.podium = p.id "
						+ "      and e.name=" + name + ";", null);
	}

	public Cursor GetSinglePerformance(int id) {
		return db.rawQuery(
				"SELECT e.name, e.genre, p.latitude,p.longitude,e.start_time,e.end_time"
						+ "FROM " + TABLE_EVENT + " e " + "     ,"
						+ TABLE_PODIUM + " p " + "WHERE e.podium = p.id "
						+ "      and e.id=" + id + ";", null);
	}

	/**
	 * List Podia
	 */
	public Cursor listPodia() {
		return db.query(TABLE_PODIUM, new String[] { KEY_ID, KEY_NAME,
				KEY_DESCRIPTION, KEY_LATITUDE, KEY_LONGITUDE, KEY_ICON_MAPS },
				null, null, null, null, null);
	}

	/**
	 * Update Artist
	 */
	public void updateArtist(int id, String name, String description) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		db.update(TABLE_ARTIST, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Update Event
	 */
	public void updateEvent(int id, int podium, int artist, String startTime,
			String endTime, String genre) {
		ContentValues values = new ContentValues();
		values.put(KEY_ID, id);
		values.put(KEY_PODIUM, podium);
		values.put(KEY_ARTIST, artist);
		values.put(KEY_START_TIME, startTime);
		values.put(KEY_END_TIME, endTime);
		values.put(KEY_GENRE, genre);
		db.update(TABLE_EVENT, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Update Podium
	 */
	public void updatePodium(int id, String name, String description,
			Double lat, Double lon, byte[] mapsIcon) {
		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name);
		values.put(KEY_DESCRIPTION, description);
		values.put(KEY_LATITUDE, lat);
		values.put(KEY_LONGITUDE, lon);
		values.put(KEY_ICON_MAPS, mapsIcon);
		db.update(TABLE_PODIUM, values, KEY_ID + "=" + id, null);
	}

	/**
	 * Retrieves all events of a given podium
	 */
	public Cursor getEventsForPodium(int podiumId) throws SQLException {
		Cursor cursor = db.query(true, TABLE_EVENT,
				new String[] { KEY_ID, KEY_PODIUM, KEY_ARTIST, KEY_START_TIME,
						KEY_END_TIME, KEY_GENRE }, KEY_PODIUM + "=" + podiumId,
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public Cursor getEventsForPodium(String name) throws SQLException {
		Cursor cursor = db.query(true, TABLE_EVENT,
				new String[] { KEY_ID, KEY_PODIUM, KEY_ARTIST, KEY_START_TIME,
						KEY_END_TIME, KEY_GENRE },
				KEY_NAME + "='" + name + "'", null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**********************************************************/
	/******************** Helper functions ********************/
	/**********************************************************/

	/**
	 * Get Event Object from Cursor
	 * 
	 * @param c
	 * @return Event
	 */
	public static Event getEventFromCursor(Cursor c) {
		Event event = new Event();
		event.setId(c.getInt(c.getColumnIndex(KEY_ID)));
		event.setArtist(c.getInt(c.getColumnIndex(KEY_ARTIST)));
		event.setPodium(c.getInt(c.getColumnIndex(KEY_PODIUM)));
		event.setStartTime(c.getString(c.getColumnIndex(KEY_START_TIME)));
		event.setEndTime(c.getString(c.getColumnIndex(KEY_END_TIME)));
		event.setGenre(c.getString(c.getColumnIndex(KEY_GENRE)));
		return event;
	}

        /**
	 * Get Podium Object from Cursor
	 * 
	 * @param c
	 * @return Event
	 */
	public static Podium getPodiumFromCursor(Cursor c) {
		Podium podium = new Podium();
		podium.setId(c.getInt(c.getColumnIndex(KEY_ID)));
		podium.setName(c.getString(c.getColumnIndex(KEY_NAME)));
		podium.setDescription(c.getString(c.getColumnIndex(KEY_DESCRIPTION)));
		podium.setLatitude(c.getDouble(c.getColumnIndex(KEY_LATITUDE)));
		podium.setLongitude(c.getDouble(c.getColumnIndex(KEY_LONGITUDE)));
		podium.setMapsIcon(c.getBlob(c.getColumnIndex(KEY_ICON_MAPS)));
		return podium;
	}
}